﻿
using System;
using System.Collections.Generic;
using System.Configuration;
using System.ComponentModel;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.DirectoryServices;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace Shuba.Web.ws
{
    /// <summary>
    /// Summary description for sinc
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class sinc : System.Web.Services.WebService
    {
        static Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();
        static string DBServer = ConfigurationManager.AppSettings["db.server"];
        static string DBName = ConfigurationManager.AppSettings["db.name"];
        static string DBUser = ConfigurationManager.AppSettings["db.user"];
        static string DBPass = Encryption.Decrypt(ConfigurationManager.AppSettings["db.pwd"]);
        static string cn = String.Format("Data Source = {0}; Initial Catalog = {1}; User Id = {2}; Password = {3}; Connect Timeout = 30; Min Pool Size = 5", DBServer, DBName, DBUser, DBPass);
        static DbProviderFactory provider = DbProviderFactories.GetFactory("System.Data.SqlClient");

        private static Database CreateDatabase()
        {
            return new GenericDatabase(cn, provider);
        }

        private static string ConnStr()
        {
            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();
            string DBServer = ConfigurationManager.AppSettings["db.server"];
            string DBName = ConfigurationManager.AppSettings["db.name"];
            string DBUser = ConfigurationManager.AppSettings["db.user"];
            string DBPass = Encryption.Decrypt(ConfigurationManager.AppSettings["db.pwd"]);
            return "Data Source = " + DBServer + "; Initial Catalog = " + DBName + "; User Id = " + DBUser + "; Password = " + DBPass + ";";
        }

        [WebMethod]
        public string CheckLDAPSPADAUser(string LoginUser, string LoginPass, string Unit)
        {
            /*tambahan untuk trace lamanya login
            LogFile(String.Format("Trace spada : Receive parameter from spada with NIP : {0} time : {1} call CheckLDAPSPADAUser", LoginUser, DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss:fff")));
            */

            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();

            string UserUnitCode = "~";
            string SPADADBEncCode = Encryption.Decrypt(ConfigurationManager.AppSettings["enc.code.spada"]);
            string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

            if (IsLDAPUser(LoginUser, LoginPass))
            {
                /*tambahan untuk trace lamanya login
                LogFile(String.Format("Trace spada : Start cek from user mapping spaweb with NIP : {0} time : {1} call CheckLDAPSPADAUser", LoginUser, DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss:fff")));
                */

                object result;
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
                                 SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK)

                                 SELECT TOP 1 BRANCH_ID FROM (SELECT LTRIM(RTRIM(BRANCH_ID)) BRANCH_ID, 1 AS ID 
	                             FROM V_USER (NOLOCK) 
	                             WHERE 
                                    (LTRIM(RTRIM(APP_ID)) LIKE @APP_ID) 
                                    AND (LTRIM(RTRIM(NIP)) LIKE @NIP) 
                                 UNION 
                                 SELECT	BRANCH_ID, 0 AS ID 
                                 FROM TBL_ALTERNATE_UM (NOLOCK)  
                                 WHERE  
                                    (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                                    AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                                    AND BRANCH_ID = @BRANCH_ID AND (ALTERNATE_FOR = 'UM' OR ALTERNATE_FOR = 'LA') AND NIP = @NIP AND IS_ACTIVE = '1') X ORDER BY ID ");

                db.AddInParameter(cmd, "@APP_ID", DbType.String, SPADAAppCode);
                db.AddInParameter(cmd, "@NIP", DbType.String, LoginUser);
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, Unit);
                result = db.ExecuteScalar(cmd);

                string unit = result == null ? "na" : Convert.ToString(result);
                unit = unit == "" ? "na" : unit;
                UserUnitCode = unit + "," + SPADADBEncCode;

                /*tambahan untuk trace lamanya login
                LogFile(String.Format("Trace spada : End cek from user mapping spaweb with NIP : {0} time : {1} call CheckLDAPSPADAUser", LoginUser, DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss:fff")));
                */

                /*tambahan untuk trace lamanya login
                LogFile(String.Format("Trace spada : Send parameter unit and enc code from spaweb to spada with NIP : {0} time : {1} call CheckLDAPSPADAUser", LoginUser, DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss:fff")));
                */

                return UserUnitCode;
            }
            else
            {
                return UserUnitCode + "," + SPADADBEncCode;
            }
        }

        [WebMethod]
        public DataSet GetSpadaUpdate()
        {
            SqlConnection sqlCon = null;
            SqlDataAdapter sqlDA = null;
            DataSet ds = new DataSet();

            try
            {
                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }

                string query = @"SELECT [DATE_FILE_UPLOAD],[DATE_FILE_MODIFIED]
                                      ,[FILE_SIZE_UPLOAD],[PATH_FILE_UPLOAD]
                                      ,[NAME_FOLDER_UPLOAD],[FILE_NAME_UPLOAD]
                                      ,[ENTRY_BY],[ENTRY_DATE]
                                      ,[UPDATE_BY],[UPDATE_DATE]
                                FROM TBL_MS_SPADA_FILE_VERSION (NOLOCK) 
                                WHERE [UPDATE_DATE] = (SELECT MAX([UPDATE_DATE]) 
                                                    FROM TBL_MS_SPADA_FILE_VERSION AS F 
                                                    WHERE SUBSTRING(F.[FILE_NAME_UPLOAD], 1, CHARINDEX('~', F.[FILE_NAME_UPLOAD]) - 1) = SUBSTRING(TBL_MS_SPADA_FILE_VERSION.[FILE_NAME_UPLOAD], 1, CHARINDEX('~', TBL_MS_SPADA_FILE_VERSION.[FILE_NAME_UPLOAD]) - 1)AND [CHECKSUM] = 0);";
                sqlDA = new SqlDataAdapter(query, sqlCon);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                sqlDA.Fill(ds, "TBL_MS_SPADA_FILE_VERSION");

                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDA != null)
                {
                    sqlDA.Dispose();
                }
                if (sqlCon != null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
        }

        bool IsLDAPUser(string LDAPUser, string LDAPPass)
        {
            if (basePage.ByPassAD())
                return true; //FOR TESTING PURPOSE ONLY

            /*tambahan untuk trace lamanya login
            LogFile(String.Format("Trace spada : Start cek user from LDAP with NIP : {0} time : {1} call IsLDAPUser", LDAPUser, DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss:fff")));
            */

            string LDAPServer = ConfigurationManager.AppSettings["ldap"];

            DirectoryEntry entry = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass);
            try
            {
                string EName = entry.Name; //Untuk Trap Error (LDAP tidak dapat) atau berhasil

                //tambahan untuk trace lamanya login
                //LogFile(String.Format("Trace spada : Finish cek user from LDAP with NIP : {0} time : {1} call IsLDAPUser", LDAPUser, DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss:fff")));
                //end

                return true;
            }
            catch
            {
                return false;
            }
        }

        [WebMethod]
        public bool CheckService()
        {
            return true;
        }
    }
}